﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:LOAN_CONTRACT
	/// </summary>
	public partial class LOAN_CONTRACT : BaseDAL<GSLMS.Entity.TB_LOAN_CONTRACT>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public LOAN_CONTRACT()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_LOAN_CONTRACT model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PROID", model.PROID),
								new OracleParameter("P_APPLYID", model.APPLYID),
								new OracleParameter("P_CUSTNAME", model.CUSTNAME),
								new OracleParameter("P_CUSTNO", model.CUSTNO),
								new OracleParameter("P_APPLYDATE", model.APPLYDATE),
								new OracleParameter("P_AUDITAMT", model.AUDITAMT),
								new OracleParameter("P_AUDITPHASE", model.AUDITPHASE),
								new OracleParameter("P_AUDITDATE", model.AUDITDATE),
								new OracleParameter("P_GENERALRATE", model.GENERALRATE),
								new OracleParameter("P_RATE", model.RATE),
								new OracleParameter("P_MANAGEFEERATE", model.MANAGEFEERATE),
								new OracleParameter("P_PAYMOD", model.PAYMOD),
								new OracleParameter("P_BUSSTYPE", model.BUSSTYPE),
								new OracleParameter("P_STARTDATE", model.STARTDATE),
								new OracleParameter("P_ENDDATE", model.ENDDATE),
								new OracleParameter("P_CONTRACTNO", model.CONTRACTNO),
								new OracleParameter("P_TICKETNO", model.TICKETNO),
								new OracleParameter("P_TICKETDATE", model.TICKETDATE),
								new OracleParameter("P_HANDLER", model.HANDLER),
								new OracleParameter("P_ADVAMT", model.ADVAMT),
								new OracleParameter("P_STATUS", model.STATUS),
								new OracleParameter("P_LOANSTATUS", model.LOANSTATUS),
								new OracleParameter("P_REMARK", model.REMARK),
								new OracleParameter("P_CONFIRMBY", model.CONFIRMBY),
								new OracleParameter("P_CONFIRMDATE", model.CONFIRMDATE),
								new OracleParameter("P_CREATEBY", model.CREATEBY),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE),
								new OracleParameter("P_TOUSER", model.TOUSER),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID)};
  
			for (int i = 0; i < 30; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_loan.proc_contract_add", parms);
                if (model.contractAccList != null && model.contractAccList.Count > 0)
                {
                    flag = AddAccount(model.contractAccList,model.PROID);
                }
                else
                {
                    flag = true;
                }
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_LOAN_CONTRACT model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PROID", model.PROID),
								new OracleParameter("P_APPLYID", model.APPLYID),
								new OracleParameter("P_CUSTNAME", model.CUSTNAME),
								new OracleParameter("P_CUSTNO", model.CUSTNO),
								new OracleParameter("P_APPLYDATE", model.APPLYDATE),
								new OracleParameter("P_AUDITAMT", model.AUDITAMT),
								new OracleParameter("P_AUDITPHASE", model.AUDITPHASE),
								new OracleParameter("P_AUDITDATE", model.AUDITDATE),
								new OracleParameter("P_GENERALRATE", model.GENERALRATE),
								new OracleParameter("P_RATE", model.RATE),
								new OracleParameter("P_MANAGEFEERATE", model.MANAGEFEERATE),
								new OracleParameter("P_PAYMOD", model.PAYMOD),
								new OracleParameter("P_BUSSTYPE", model.BUSSTYPE),
								new OracleParameter("P_STARTDATE", model.STARTDATE),
								new OracleParameter("P_ENDDATE", model.ENDDATE),
								new OracleParameter("P_CONTRACTNO", model.CONTRACTNO),
								new OracleParameter("P_TICKETNO", model.TICKETNO),
								new OracleParameter("P_TICKETDATE", model.TICKETDATE),
								new OracleParameter("P_HANDLER", model.HANDLER),
								new OracleParameter("P_ADVAMT", model.ADVAMT),
								new OracleParameter("P_STATUS", model.STATUS),
								new OracleParameter("P_LOANSTATUS", model.LOANSTATUS),
								new OracleParameter("P_REMARK", model.REMARK),
								new OracleParameter("P_CONFIRMBY", model.CONFIRMBY),
								new OracleParameter("P_CONFIRMDATE", model.CONFIRMDATE),
								new OracleParameter("P_CREATEBY", model.CREATEBY),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE),
								new OracleParameter("P_TOUSER", model.TOUSER),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID)};
  
			for (int i = 0; i < 30; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_loan.proc_contract_update", parms);
                if (model.contractAccList != null && model.contractAccList.Count > 0)
                {
                    flag = AddAccount(model.contractAccList,model.PROID);
                }
                else
                {
                    flag = true;
                }
                
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

        /// <summary>
        ///  增加一条数据
        /// </summary>
        public  bool AddAccount(List<GSLMS.Entity.TB_LOAN_CONTRACTACC> list,long proID)
        {
            bool flag = false;
            if (list != null && list.Count > 0)
            {
                foreach (var model in list)
                {
                    OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_PROID",proID),
								new OracleParameter("P_CONID", model.CONID),
								new OracleParameter("P_BANKNAME", model.BANKNAME),
								new OracleParameter("P_SUBBANK", model.SUBBANK),
								new OracleParameter("P_ACCOUNTNAME", model.ACCOUNTNAME),
								new OracleParameter("P_ACCOUNTNO", model.ACCOUNTNO),
								new OracleParameter("P_ACCOUNTTYPE", model.ACCOUNTTYPE),
                                new OracleParameter("P_ACCOUNTAMT", model.ACCOUNTAMT)
                           };

                    for (int i = 0; i < 9; i++)
                    {
                        parms[i].Direction = ParameterDirection.Input;
                    }

                    try
                    {
                        OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_loan.proc_contractacc_add", parms);
                        flag = true;
                    }
                    catch (Exception ex)
                    {
                        log.ExceptionHand(ex);
                        flag = false;
                    }
                }
            }
            else
            {
                flag = true;
            }
            return flag;
        }

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
				OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_apply.proc_loan_contract_delete", parms);
				flag = true;
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_LOAN_CONTRACT DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_LOAN_CONTRACT model = new GSLMS.Entity.TB_LOAN_CONTRACT();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["PROID"]!=null && dr["PROID"].ToString()!="")
				{
					model.PROID=OracleHelper.ToLong(dr["PROID"].ToString());
				}
				if(dr["APPLYID"]!=null && dr["APPLYID"].ToString()!="")
				{
					model.APPLYID=dr["APPLYID"].ToString();
				}
				if(dr["CUSTNAME"]!=null && dr["CUSTNAME"].ToString()!="")
				{
					model.CUSTNAME=dr["CUSTNAME"].ToString();
				}
				if(dr["CUSTNO"]!=null && dr["CUSTNO"].ToString()!="")
				{
					model.CUSTNO=dr["CUSTNO"].ToString();
				}
				if(dr["APPLYDATE"]!=null && dr["APPLYDATE"].ToString()!="")
				{
					model.APPLYDATE=DateTime.Parse(dr["APPLYDATE"].ToString());
				}
				if(dr["AUDITAMT"]!=null && dr["AUDITAMT"].ToString()!="")
				{
					model.AUDITAMT=OracleHelper.ToDecimal(dr["AUDITAMT"].ToString());
				}
				if(dr["AUDITPHASE"]!=null && dr["AUDITPHASE"].ToString()!="")
				{
                    model.AUDITPHASE = OracleHelper.ToInt(dr["AUDITPHASE"].ToString());
				}
				if(dr["AUDITDATE"]!=null && dr["AUDITDATE"].ToString()!="")
				{
					model.AUDITDATE=DateTime.Parse(dr["AUDITDATE"].ToString());
				}
				if(dr["GENERALRATE"]!=null && dr["GENERALRATE"].ToString()!="")
				{
					model.GENERALRATE=OracleHelper.ToDecimal(dr["GENERALRATE"].ToString());
				}
				if(dr["RATE"]!=null && dr["RATE"].ToString()!="")
				{
					model.RATE=OracleHelper.ToDecimal(dr["RATE"].ToString());
				}
				if(dr["MANAGEFEERATE"]!=null && dr["MANAGEFEERATE"].ToString()!="")
				{
					model.MANAGEFEERATE=OracleHelper.ToDecimal(dr["MANAGEFEERATE"].ToString());
				}
				if(dr["PAYMOD"]!=null && dr["PAYMOD"].ToString()!="")
				{
                    model.PAYMOD = OracleHelper.ToInt(dr["PAYMOD"].ToString());
				}
				if(dr["BUSSTYPE"]!=null && dr["BUSSTYPE"].ToString()!="")
				{
                    model.BUSSTYPE = OracleHelper.ToInt(dr["BUSSTYPE"].ToString());
				}
				if(dr["STARTDATE"]!=null && dr["STARTDATE"].ToString()!="")
				{
					model.STARTDATE=DateTime.Parse(dr["STARTDATE"].ToString());
				}
				if(dr["ENDDATE"]!=null && dr["ENDDATE"].ToString()!="")
				{
					model.ENDDATE=DateTime.Parse(dr["ENDDATE"].ToString());
				}
				if(dr["CONTRACTNO"]!=null && dr["CONTRACTNO"].ToString()!="")
				{
					model.CONTRACTNO=dr["CONTRACTNO"].ToString();
				}
				if(dr["TICKETNO"]!=null && dr["TICKETNO"].ToString()!="")
				{
					model.TICKETNO=dr["TICKETNO"].ToString();
				}
				if(dr["TICKETDATE"]!=null && dr["TICKETDATE"].ToString()!="")
				{
					model.TICKETDATE=DateTime.Parse(dr["TICKETDATE"].ToString());
				}
				if(dr["HANDLER"]!=null && dr["HANDLER"].ToString()!="")
				{
					model.HANDLER=dr["HANDLER"].ToString();
				}
				if(dr["ADVAMT"]!=null && dr["ADVAMT"].ToString()!="")
				{
					model.ADVAMT=OracleHelper.ToDecimal(dr["ADVAMT"].ToString());
				}
				if(dr["STATUS"]!=null && dr["STATUS"].ToString()!="")
				{
                    model.STATUS = OracleHelper.ToInt(dr["STATUS"].ToString());
				}
				if(dr["LOANSTATUS"]!=null && dr["LOANSTATUS"].ToString()!="")
				{
                    model.LOANSTATUS = OracleHelper.ToInt(dr["LOANSTATUS"].ToString());
				}
				if(dr["REMARK"]!=null && dr["REMARK"].ToString()!="")
				{
					model.REMARK=dr["REMARK"].ToString();
				}
				if(dr["CONFIRMBY"]!=null && dr["CONFIRMBY"].ToString()!="")
				{
					model.CONFIRMBY=dr["CONFIRMBY"].ToString();
				}
				if(dr["CONFIRMDATE"]!=null && dr["CONFIRMDATE"].ToString()!="")
				{
					model.CONFIRMDATE=DateTime.Parse(dr["CONFIRMDATE"].ToString());
				}
				if(dr["CREATEBY"]!=null && dr["CREATEBY"].ToString()!="")
				{
					model.CREATEBY=dr["CREATEBY"].ToString();
				}
				if(dr["CREATEDATE"]!=null && dr["CREATEDATE"].ToString()!="")
				{
					model.CREATEDATE=DateTime.Parse(dr["CREATEDATE"].ToString());
				}
				if(dr["TOUSER"]!=null && dr["TOUSER"].ToString()!="")
				{
					model.TOUSER=dr["TOUSER"].ToString();
				}
				if(dr["SYSTEMID"]!=null && dr["SYSTEMID"].ToString()!="")
				{
                    model.SYSTEMID = OracleHelper.ToInt(dr["SYSTEMID"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_LOAN_CONTRACT GetByID(long ID)
		{
			GSLMS.Entity.TB_LOAN_CONTRACT sModel = new GSLMS.Entity.TB_LOAN_CONTRACT();
			string sql = "";
			sql = "select * from TB_LOAN_CONTRACT where id=" + ID.ToString();
			DataSet ds = OracleHelper.ExecuteDataset(sql);
			if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				sModel = DataRowToModel(ds.Tables[0].Rows[0]);
			}
			else
			{
			return null;
			}
			return sModel;
		}

		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_LOAN_CONTRACT> GetList(GSLMS.Entity.TB_LOAN_CONTRACT searchParam)
		{
			var list=new List<GSLMS.Entity.TB_LOAN_CONTRACT>();
			DataTable dt = null;
			string tblName = "TB_LOAN_CONTRACT";
			string sqlWhere="";
			string strSort="";
			int pageSize=20;
			int pageIndex=1;
			string strFields="*";
			int totalCount = 0;
			int countFlag = 1;

            //是否计算总数
            countFlag = searchParam.Pagination.ReCount ? 1 : 0;
            pageSize = (searchParam.Pagination.PageSize == -1 || searchParam.Pagination.PageSize > 0) ? searchParam.Pagination.PageSize : 15;
            pageIndex = searchParam.Pagination.CurrentIndex > 0 ? searchParam.Pagination.CurrentIndex : 1;
            strSort = searchParam.StrSort;


            if (searchParam.CUSTNAME != null && searchParam.CUSTNAME.Trim() != "")
            {
                sqlWhere += " and CUSTNAME like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CUSTNAME.Trim()) + "%'";
            }

            if (searchParam.CONTRACTNO != null && searchParam.CONTRACTNO.Trim() != "")
            {
                sqlWhere += " and (CONTRACTNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CONTRACTNO.Trim()) + "%' or ";
                sqlWhere += " and (TICKETNO like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.CONTRACTNO.Trim()) + "%') ";
            }

            if (searchParam.APPLYID != null && searchParam.APPLYID.Trim() != "")
            {
                sqlWhere += " and applyno like '%" + OracleHelper.ReplaceUnsafeSql(searchParam.APPLYID.Trim()) + "%'";
            }

            if (searchParam.SYSTEMID > -1)
            {
                sqlWhere += " and to_number(systemid)=" + searchParam.SYSTEMID.ToString();
            }

            if (searchParam.STATUS == 0&&searchParam.STATUS2==1)
            {
                sqlWhere += " and (status=4 or status=5)";
            }

            if (searchParam.STATUS == 1 && searchParam.STATUS2 == 0)
            {
                sqlWhere += " and (status<>4 and status<>5)";
            }

            if (searchParam.BUSSTYPE>0)
            {
                sqlWhere += " and BUSSTYPE="+searchParam.BUSSTYPE.ToString();
            }

			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);

            if (searchParam.Pagination.ReCount)
            {
                searchParam.RowCount = totalCount;
                searchParam.Pagination.RowCount = totalCount;
            }

            
            if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_LOAN_CONTRACT model = new GSLMS.Entity.TB_LOAN_CONTRACT();
					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			else
			{
				return null;
			}
			return list;
		}

        /// <summary>
        ///DataRowToModel 
        /// </summary>
        private GSLMS.Entity.TB_LOAN_CONTRACTACC DataRowToContractAccModel(DataRow dr)
        {
            if (dr != null && dr.ItemArray.Length > 0)
            {
                GSLMS.Entity.TB_LOAN_CONTRACTACC model = new GSLMS.Entity.TB_LOAN_CONTRACTACC();
                if (dr["ID"] != null && dr["ID"].ToString() != "")
                {
                    model.ID = OracleHelper.ToLong(dr["ID"].ToString());
                }
                if (dr["PROID"] != null && dr["PROID"].ToString() != "")
                {
                    model.PROID = OracleHelper.ToLong(dr["PROID"].ToString());
                }
                if (dr["CONID"] != null && dr["CONID"].ToString() != "")
                {
                    model.CONID = OracleHelper.ToLong(dr["CONID"].ToString());
                }
                if (dr["BANKNAME"] != null && dr["BANKNAME"].ToString() != "")
                {
                    model.BANKNAME = dr["BANKNAME"].ToString();
                }
                if (dr["SUBBANK"] != null && dr["SUBBANK"].ToString() != "")
                {
                    model.SUBBANK = dr["SUBBANK"].ToString();
                }
                if (dr["ACCOUNTNAME"] != null && dr["ACCOUNTNAME"].ToString() != "")
                {
                    model.ACCOUNTNAME = dr["ACCOUNTNAME"].ToString();
                }
                if (dr["ACCOUNTNO"] != null && dr["ACCOUNTNO"].ToString() != "")
                {
                    model.ACCOUNTNO = dr["ACCOUNTNO"].ToString();
                }
                if (dr["ACCOUNTTYPE"] != null && dr["ACCOUNTTYPE"].ToString() != "")
                {
                    model.ACCOUNTTYPE = OracleHelper.ToInt(dr["ACCOUNTTYPE"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 通过立项申请记录ID提取合同信息
		/// </summary>
        public GSLMS.Entity.TB_LOAN_CONTRACT GetByProID(long ProID)
        {
            GSLMS.Entity.TB_LOAN_CONTRACT sModel = new GSLMS.Entity.TB_LOAN_CONTRACT();

            OracleParameter[] parms = new OracleParameter[] { 
              new OracleParameter("p_proid",ProID),
              new OracleParameter("p_cur",OracleType.Cursor),
              new OracleParameter("p_acur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;
            parms[2].Direction = ParameterDirection.Output;


            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_loan.proc_contract_get", parms);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                sModel = DataRowToModel(ds.Tables[0].Rows[0]);

                if (ds.Tables.Count > 1&&ds.Tables[1].Rows.Count>0)
                {
                    var accList = new List<GSLMS.Entity.TB_LOAN_CONTRACTACC>();
                    for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
                    {
                        var accModel = new GSLMS.Entity.TB_LOAN_CONTRACTACC();
                        accModel=DataRowToContractAccModel(ds.Tables[1].Rows[i]);
                        accList.Add(accModel);
                    }

                    sModel.contractAccList = accList;
                }
            }
            else
            {
                return null;
            }
            return sModel;
        }

        



		#endregion  Method
	}
}

